<?php  if (!defined('BASEPATH')) exit('No direct script access allowed');

function xls_user($product_id = 0)
{
	$ci =& get_instance();
	
	if($product_id)
	{
		$sql = 'SELECT m.id as userid, m.username, m.email, m.phone FROM members m WHERE m.id in(SELECT o.userid FROM `order` o WHERE productid='.$product_id. ')';
	}
	else
	{
		$sql = 'SELECT id as userid, username, email, phone FROM members';
	}
	$query = $ci->db->query($sql);
	
	if ($query->num_rows() > 0)
	{
	    $users = $query->result_array();
	}
	else 
	{
		$users = array();
	}
    $continar = array(1 => array ('ID', '用戶姓名', '電郵', '電話'));
    foreach($users as $user)
    {
    	array_push($continar, array($user['userid'], $user['username'], $user['email'], $user['phone']));
    }
    $ci->load->library('Excel_XML');
	$xls = new Excel_XML('UTF-8', false, '幸買團購');
	$xls->addArray($continar);
	$xls->generateXML('members-xls');
}
function xls_order($product_id = 0)
{
	$ci =& get_instance();
	
	if($product_id)
	{
		$sql='select o.*,m.username,m.email,p.nowprice,p.successnum, p.name from `order` o left join members m on o.userid= m.id left join product p on o.productid=p.id where o.productid = '.intval($product_id);
	}
	else
	{
		$sql='select o.*,m.username,m.email,p.nowprice,p.successnum, p.name from `order` o left join members m on o.userid= m.id left join product p on o.productid=p.id';
	}
	$query = $ci->db->query($sql);
	
	if ($query->num_rows() > 0)
	{
	    $orders = $query->result_array();
	    
	}
	else 
	{
		$orders = array();
	}
	$continar = array(1 => array ('訂單號 ', '商品名稱', '單價', '數量', '付款時間 ', '購買人'));
    foreach($orders as $order)
    {
    	array_push($continar, array($order['orderid'], strip_tags($order['name']), $order['nowprice'], $order['productnum'], date('Y-m-d H:i:s',$order['buytime']), $order['username']));
    }
    $ci->load->library('Excel_XML');
	$xls = new Excel_XML('UTF-8', false, '幸買團購');
	$xls->addArray($continar);
	$xls->generateXML('orders-xls');
}
function xls_ticket($product_id = 0)
{
	$ci =& get_instance();
	
	if($product_id)
	{
		$sql='select ticketid,uid,productid,p.perioddate, orderid,number,t.password,usetime,t.status, m.username, p.name,p.successnum, p.name from ticket t left join product p on t.productid= p.id left join members m on m.id = t.uid where t.productid = '.intval($product_id);
	}
	else
	{
		$sql='select ticketid,uid,productid,orderid,number,t.password,usetime,t.status, m.username, p.name,p.successnum, p.name from ticket t left join product p on t.productid= p.id left join members m on m.id = t.uid';
	}
	$query = $ci->db->query($sql);
	
	if ($query->num_rows() > 0)
	{
	    $tickets = $query->result_array();
	}
	else
	{
		$tickets = array();
	}
	$continar = array(1 => array ('優惠券編號', '産品名稱', '購買人', '有效期至', '參考編號 ', '驗證碼'));
    foreach($tickets as $ticket)
    {
    	array_push($continar, array($ticket['number'], strip_tags($ticket['name']), $ticket['username'], date('Y-m-d H:i:s',$ticket['perioddate']), 'LB00'. $ticket['productid']. '-'. $ticket['number'], authcode($ticket['password'],'DECODE')));
    }
    $ci->load->library('Excel_XML');
	$xls = new Excel_XML('UTF-8', false, '幸買團購');
	$xls->addArray($continar);
	$xls->generateXML('tickets-xls');
}
?>